﻿using Blog190311.Models;
using Blog190311.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Blog190311.Areas.Back.Models.DAL
{
    public class RecommendService
    {
        /// <summary>
        /// 多条件分页查询
        /// </summary>
        /// <param name="limit"></param>
        /// <param name="page"></param>
        /// <param name="weizhi"></param>
        /// <param name="quanzhong"></param>
        /// <returns></returns>
        public List<RecArticle> GetTuijian(int limit, int page, string weizhi, string quanzhong)
        {
            List<RecArticle> list = new List<RecArticle>();
            string sqlWhere = "";
            if (!string.IsNullOrWhiteSpace(weizhi))
            {
                sqlWhere += string.Format(" and PosId>='{0}'", weizhi);
            }
            if (!string.IsNullOrWhiteSpace(quanzhong))
            {
                sqlWhere += string.Format(" and Weight>='{0}'", quanzhong);
            }
            string sqlStr = string.Format(" select top ({0}) * from RecArticles where Id not in (select top({1})Id from RecArticles)", limit, limit * (page - 1));
            sqlStr = sqlStr + sqlWhere;
            SqlDataReader dr = DBHelper.ExecuteReader(sqlStr);
            while (dr.Read())
            {
                list.Add(new RecArticle()
                {
                    Id = Convert.ToInt32(dr["id"]),
                    ArticleId = Convert.ToInt32(dr["articleid"]),
                    PosId = Convert.ToInt32(dr["posid"]),
                    Weight = Convert.ToInt32(dr["weight"])
                });
            }
            return list;
        }

        /// <summary>
        /// 获取满足条件的数据条数
        /// </summary>
        /// <param name="weizhi"></param>
        /// <param name="quanzhong"></param>
        /// <returns></returns>
        public int GetCount(string weizhi, string quanzhong)
        {
            string sqlWhere = " where 1=1";
            if (!string.IsNullOrWhiteSpace(weizhi))
            {
                sqlWhere += string.Format(" and PosId>='{0}'", weizhi);
            }
            if (!string.IsNullOrWhiteSpace(quanzhong))
            {
                sqlWhere += string.Format(" and Weight>='{0}'", quanzhong);
            }
            string sqlStr = "select COUNT(*) from RecArticles ";
            sqlStr = sqlStr + sqlWhere;
            object re = DBHelper.ExecuteScalar(sqlStr);
            if (re != null)
            {
                return Convert.ToInt32(re);
            }
            return 0;
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="recarticle"></param>
        /// <returns></returns>
        public int SaveTui(RecArticle recarticle)
        {
            string sqlStr = string.Format("insert into RecArticles (ArticleId ,PosId ,Weight)values({0},{1},{2})", recarticle.ArticleId, recarticle.PosId, recarticle.Weight);
            int num = DBHelper.ExecuteNonQuery(sqlStr);
            return num;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="recarticle"></param>
        /// <returns></returns>
        public int UpdateTui(RecArticle recarticle)
        {
            string sqlStr = string.Format("update RecArticles set ArticleId={0},PosId={1},Weight={2} where Id={3}", recarticle.ArticleId, recarticle.PosId, recarticle.Weight, recarticle.Id);
            int num = DBHelper.ExecuteNonQuery(sqlStr);
            return num;
        }

        /// <summary>
        /// 根据id获取当前数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public RecArticle GetTuiById(int id)
        {
            string sqlStr = string.Format("select * from RecArticles where Id={0}", id);
            SqlDataReader dr = DBHelper.ExecuteReader(sqlStr);
            if (dr.Read())
            {
                RecArticle recarticle = new RecArticle()
                {
                    Id = Convert.ToInt32(dr["id"]),
                    ArticleId = Convert.ToInt32(dr["articleid"]),
                    PosId = Convert.ToInt32(dr["posid"]),
                    Weight = Convert.ToInt32(dr["weight"])
                };
                return recarticle;
            }
            return null;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelTui(int id)
        {
            string sqlStr = string.Format("delete from RecArticles where Id={0}", id);
            int num = DBHelper.ExecuteNonQuery(sqlStr);
            return num;
        }
    }
}